VERSION 5.00
Begin VB.Form frmAddFromCSV 
   BorderStyle     =   4  'Fixed ToolWindow
   Caption         =   "Add data from CSV(Excel)"
   ClientHeight    =   7230
   ClientLeft      =   45
   ClientTop       =   315
   ClientWidth     =   11790
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7230
   ScaleWidth      =   11790
   ShowInTaskbar   =   0   'False
   Begin VB.CommandButton cmdClear 
      Caption         =   "Clear"
      Height          =   495
      Left            =   6840
      TabIndex        =   5
      Top             =   6480
      Width           =   1215
   End
   Begin VB.CommandButton cmdApply 
      Caption         =   "Apply"
      Height          =   495
      Left            =   8520
      TabIndex        =   4
      Top             =   6480
      Width           =   1215
   End
   Begin VB.CommandButton cmdExit 
      Caption         =   "Exit"
      Height          =   495
      Left            =   10200
      TabIndex        =   3
      Top             =   6480
      Width           =   1215
   End
   Begin VB.TextBox txtCSV 
      Height          =   5295
      Left            =   240
      MultiLine       =   -1  'True
      ScrollBars      =   2  'Vertical
      TabIndex        =   2
      Top             =   840
      Width           =   11295
   End
   Begin VB.ComboBox cmbSort 
      Height          =   315
      Left            =   1800
      TabIndex        =   0
      Text            =   "Combo1"
      Top             =   240
      Width           =   3615
   End
   Begin VB.Label Label1 
      Caption         =   "Classification"
      Height          =   375
      Left            =   240
      TabIndex        =   1
      Top             =   240
      Width           =   1455
   End
End
Attribute VB_Name = "frmAddFromCSV"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Type BusCustomer
    id As String
    temp_userid As Long
    chs_name As String
    eng_name As String
    logo As String
    addr As String
    tel As String
    fax As String
    openinghrs As String
    des As String
    website As String
    email As String
    hotline As String
    mobile As String
    city_id As Integer
End Type

Dim CusInfo As BusCustomer

Private Sub init_structure()
    CusInfo.addr = ""
    CusInfo.chs_name = ""
    CusInfo.des = ""
    CusInfo.email = ""
    CusInfo.eng_name = ""
    CusInfo.fax = ""
    CusInfo.hotline = ""
    CusInfo.id = ""
    CusInfo.logo = ""
    CusInfo.mobile = ""
    CusInfo.openinghrs = ""
    CusInfo.tel = ""
    CusInfo.temp_userid = 0
    CusInfo.website = ""
    CusInfo.city_id = 2
End Sub

Private Sub GetTemp_userID()
    Dim sql As String
    Dim id As Long
    sql = "select max(temp_userid) from org_firststep"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    id = val(rs.Fields(0).value) + 1
    Call Save2Structure(1, str(id))  'temp_userid
End Sub


Private Sub Save2Database()
    Dim sql As String
    Dim sName As String
    Dim sort_id As Integer
    Dim ArrSort() As String
    If Trim(CusInfo.chs_name) = Trim(CusInfo.eng_name) Then
        sName = CusInfo.chs_name
    Else
        If Trim(CusInfo.eng_name) <> "" Then
        sName = CusInfo.chs_name & " (" & CusInfo.eng_name & ")"
        Else
        sName = CusInfo.chs_name
        End If
    End If
    
    frmSplash.lblStatus.Caption = "Saving: " & sName
    DoEvents
    
    If CusInfo.mobile <> "" Then
        CusInfo.des = CusInfo.des & vbCrLf & "Mobile: " & CusInfo.mobile
    End If
    
    ArrSort = Split(cmbSort.Text, "-")
    sort_id = Int(ArrSort(1))
    sql = "INSERT INTO `org_details`(`usr_id`, `temp_userid`, `curr_addr`, `fax`, `website`, `thirdpart_upload`,`recorder`, `self_intro`, `usr_face`, `usr_mails`, `usr_phone`,`usr_shw_name`, `sort_id`, `city_id`, `TradingHrs`) VALUES ('" & CusInfo.id & "'," & _
    val(CusInfo.temp_userid) & ",'" & AddSlashes(Trim(CusInfo.addr)) & "', '" & Trim(CusInfo.fax) & "', '" & _
    Trim(CusInfo.website) & "', 1 ,1, '" & AddSlashes(Trim(CusInfo.des)) & "', '" & AddSlashes(Trim(CusInfo.logo)) & "', '" & _
    Trim(CusInfo.email) & "', '" & Trim(CusInfo.tel) & "', '" & AddSlashes(Trim(sName)) & "', " & sort_id & ", " & _
    Trim(CusInfo.city_id) & ", '" & Trim(CusInfo.openinghrs) & "')"
    'Debug.Print sql
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql


'INSERT INTO `org_firststep`(`temp_userid`, `org_name`, `emailadd`, `tel`, `org_type`, `user_pswd`, `city_id`,
'`curr_add`, `act_pin`, `acc_status`, `date_time`, `last_log_in`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12])
sql = "INSERT INTO `org_firststep`(`temp_userid`, `org_name`, `emailadd`, `tel`, `org_type`, `user_pswd`, `city_id`, `curr_add`, `act_pin`, `acc_status`) VALUES (" & val(CusInfo.temp_userid) & _
", '" & AddSlashes(Trim(sName)) & "', '" & Trim(CusInfo.email) & "', '" & Trim(CusInfo.tel) & "', 1, 'e10adc3949ba59abbe56e057f20f883e', " & _
CusInfo.city_id & ", '" & AddSlashes(Trim(CusInfo.addr)) & "', '0', 1 )"
'Debug.Print sql
Call CloseRecordSet(rs)
OperateDatabase rs, cn, sql

'MsgBox sName & " saved!", vbOKOnly, SOFT_TITLE
sql = "INSERT INTO `cs_classification`(`temp_userid`, `FirstLevel`, `SecondLevel`) VALUES (" & val(CusInfo.temp_userid) & ", " & Int(Mid(ArrSort(1), 1, Len(ArrSort(1)) - 3)) & ", " & sort_id & ")"
Debug.Print sql
Call CloseRecordSet(rs)
OperateDatabase rs, cn, sql
End Sub

Private Sub cmdApply_Click()
    Dim iIndex, iItem, iP As Long
    Dim bInData As Boolean
    Dim sData As String
    Dim cP, Data As String

    iIndex = 0
    iItem = 0
    iP = 1
    bInData = False
    Data = ""
    sData = Trim(txtCSV.Text)
    init_structure
    frmSplash.Show
    DoEvents
    cP = Mid(sData, iP, 1)
    While cP <> ""
        'init_structure
        
        If Not bInData Then
            If cP = "," Then
                Call Save2Structure(iItem + 2, Data)
                iItem = iItem + 1
                Data = ""
            ElseIf Asc(cP) = 34 Then ' -> "
                bInData = True
            ElseIf Asc(cP) = 13 And Asc(Mid(sData, iP + 1, 1)) = 10 Then
                bInData = False
                init_structure
                Data = ""
                iItem = 0
                iP = iP + 1
            Else
                Data = Data & cP
            End If
        Else
            If Asc(cP) = 34 And Mid(sData, iP + 1, 1) = "," Then
                bInData = False
                Call Save2Structure(iItem + 2, Data)
                iItem = iItem + 1
                Data = ""
                iP = iP + 1
            Else
                Data = Data & cP
            End If
        End If
        iP = iP + 1
        cP = Mid(sData, iP, 1)
    Wend
    Unload frmSplash
    DoEvents
    MsgBox "All done!", vbOKOnly, SOFT_TITLE
End Sub

Private Sub Save2Structure(ByVal i As Integer, ByVal strData As String)
    Select Case i
        Case 0:
            CusInfo.id = "1110000" & formatn(Trim(str(CusInfo.city_id)), 2) & formatn(Trim(str(CusInfo.temp_userid)), 8)
        Case 1:
            CusInfo.temp_userid = val(strData)
            Call Save2Structure(0, str(CusInfo.temp_userid))
        Case 2:
            CusInfo.chs_name = strData
        Case 3:
            CusInfo.eng_name = strData
        Case 4:
            CusInfo.logo = strData
        Case 5:
            CusInfo.addr = strData
        Case 6:
            CusInfo.tel = strData
        Case 7:
            CusInfo.fax = strData
        Case 8:
            CusInfo.openinghrs = strData
        Case 9:
            CusInfo.des = strData
        Case 10:
            CusInfo.website = strData
        Case 11:
            CusInfo.email = strData
        Case 12:
            CusInfo.hotline = strData
        Case 13:
            CusInfo.mobile = strData
        Case Else:
            GetTemp_userID
            Save2Database
         '   init_structure
    End Select
End Sub



Private Sub cmdClear_Click()
    txtCSV.Text = ""
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub CloseRecordSet(ByRef rs As ADODB.Recordset)
On Error GoTo NoErr
        rs.Close
NoErr:
End Sub
Private Sub Form_Load()
    Dim sql As String
    Call Cn2Oversea(cn)
    On Error GoTo openDB
    sql = "SET NAMES gbk"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    
openDB:
    sql = "select * from cs_sort  order by Sort_ID ASC"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    While Not rs.EOF
        cmbSort.AddItem rs.Fields(1).value & "-" & rs.Fields(0).value
        rs.MoveNext
    Wend
    
    cmbSort.ListIndex = 0
End Sub

Private Sub Form_Unload(Cancel As Integer)
    cn.Close
End Sub
